var express = require('express');
var router = express.Router();
const db = require('../configs/db.config'); // 引入数据库连接配置文件
const fs = require('fs');
const multer = require('multer');
const xlsx = require('xlsx');
const path = require('path');
// 设置Multer存储配置
// const storage = multer.diskStorage({
//     destination: function (req, file, cb) {
//         cb(null, 'uploads/') // 文件保存路径，请确保该目录存在
//     },
//     filename: function (req, file, cb) {
//         cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname))
//     }
// });
// const upload = multer({ storage: storage })
const upload = multer({ dest: 'uploads/' })

router.post('/file',upload.single('excelFile'), function(req, res, next) {
    try {
        // 1. 读取Excel文件
        const workbook = xlsx.readFile(req.file.path);
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];

        // 2. 转换为JSON数据
        const data = xlsx.utils.sheet_to_json(worksheet);

        // 3. 数据清洗转换
        const transformedData = data.map(item => ({
            name: item['姓名'] || item['name'],
            age: parseInt(item['年龄'] || item['age']),
            department: item['部门'] || item['department']
        }));
        console.log('hhhhhh',data,transformedData)

        // // 4. 批量插入数据库
        // const connection = await pool.getConnection();
        // const [result] = await connection.query(
        //     'INSERT INTO employees (name, age, department) VALUES ?',
        //     [transformedData.map(obj => Object.values(obj))]
        // );
        // connection.release();

        // 5. 清理临时文件
        // fs.unlinkSync(req.file.path);
        //
        // res.json({
        //     code: 200,
        //     data: { affectedRows: result.affectedRows }
        // });
    } catch (error) {
        console.error(error);
        res.status(500).send('File processing error.');
    }
    // res.render('index', { title: 'Express' });
});

module.exports = router;
